package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.SoftwareDao;
import com.windfone.model.Software;
import com.windfone.model.SoftwareType;

public class SoftwareDaoImpl implements SoftwareDao {
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	String SQL_GET_SOFTWARE_BY_SOFTTYPE_ID=" SELECT * FROM  software WHERE softwareTypeId=? ";
	public List<Software> getSoftwareByTypeId(final int softwareTypeId) {
		final List<Software> result = new ArrayList<Software>();
		try {
			jdbcTemplate.query(
					SQL_GET_SOFTWARE_BY_SOFTTYPE_ID,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, softwareTypeId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Software to= new Software();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setSystem(rs.getString("system"));
							to.setSmallImagepath(rs.getString("smallImagepath"));
							to.setBigImagepath(rs.getString("bigImagepath"));
							to.setLastUpdate(rs.getString("lastUpdate"));
							to.setKeyword(rs.getString("keyword"));
							to.setDownCount(rs.getInt("downCount"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setFileName(rs.getString("fileName"));
							to.setSoftwareTypeId(rs.getInt("softwareTypeId"));
							to.setLangId(rs.getInt("langId"));
							to.setContent(rs.getString("content"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 
 
	String SQL_GET_SOFTWARE_BYID=" SELECT s.*, t.name as softwareTypeName FROM  software s ,softwaretype t WHERE  s.softwareTypeId=t.id AND  s.id=?";
//		" SELECT * FROM  software WHERE id=? ";
	public List<Software> getSoftwareById(final int softwareId) {
		final List<Software> result = new ArrayList<Software>();
		try {
			jdbcTemplate.query(
					SQL_GET_SOFTWARE_BYID,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, softwareId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Software to= new Software();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setSystem(rs.getString("system"));
							to.setVersion(rs.getString("version"));
							to.setSmallImagepath(rs.getString("smallImagepath"));
							to.setBigImagepath(rs.getString("bigImagepath"));
							to.setLastUpdate(rs.getString("lastUpdate"));
							to.setKeyword(rs.getString("keyword"));
							to.setDownCount(rs.getInt("downCount"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setFileName(rs.getString("fileName"));
							to.setSoftwareTypeId(rs.getInt("softwareTypeId"));
							to.setLangId(rs.getInt("langId"));
							to.setContent(rs.getString("content"));
							to.setSoftwareTypeName(rs.getString("softwareTypeName"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private String GET_ALL_SOFTWARE_TYPE = " SELECT * FROM softwaretype 	";
	public List<SoftwareType> getAllSoftwareType() {
		final List<SoftwareType> result = new ArrayList<SoftwareType>();
		try {
			jdbcTemplate.query(
					GET_ALL_SOFTWARE_TYPE,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							SoftwareType to= new SoftwareType();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
 							result.add(to);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 
	String SQL_UPDATE_SOFTWARE=" UPDATE software SET name=?,version=?,system=?,smallImagepath=?,bigImagepath=?,  " +
			" lastUpdate=?,softwareTypeId=?, keyword=?,downCount=?,fileName=? ,langId=?,content=?  WHERE id=?";
	public int updateSoftwareById(final Software bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				SQL_UPDATE_SOFTWARE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++, bean.getName());
						ps.setString(i++, bean.getVersion());
						ps.setString(i++, bean.getSystem());
						ps.setString(i++, bean.getSmallImagepath());
						ps.setString(i++, bean.getBigImagepath());
						ps.setString(i++, bean.getLastUpdate());
						ps.setInt(i++, bean.getSoftwareTypeId());
						ps.setString(i++, bean.getKeyword());
						ps.setInt(i++, bean.getDownCount());
						ps.setString(i++, bean.getFileName());
						ps.setInt(i++,bean.getLangId());
						ps.setString(i++,bean.getContent())	;
						ps.setInt(i++, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	
	String SQL_INSERT_SOFTWARE=" INSERT INTO software ( name,version,system,smallImagepath,bigImagepath,  " +
			" lastUpdate,softwareTypeId, keyword,downCount,fileName,langId,content) VALUES(?,?,?,?,?  , ?,?,?,?,? ,?,? )";
	public int addSoftware(final Software bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_INSERT_SOFTWARE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++, bean.getName());
						ps.setString(i++, bean.getVersion());
						ps.setString(i++, bean.getSystem());
						ps.setString(i++, bean.getSmallImagepath());
						ps.setString(i++, bean.getBigImagepath());
						ps.setString(i++, bean.getLastUpdate());
						ps.setInt(i++, bean.getSoftwareTypeId());
						ps.setString(i++, bean.getKeyword());
						ps.setInt(i++, bean.getDownCount());
						ps.setString(i++, bean.getFileName());
						ps.setInt(i++,bean.getLangId());		
						ps.setString(i++,bean.getContent());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	String SQL_DELETE_SOFTWARE=" DELETE FROM  software WHERE id=?";
	public int  deleSoftware(final Software bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					SQL_DELETE_SOFTWARE,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String SQL_GET_ACOUNT_PRODUCT=" SELECT COUNT(1) FROM  software s WHERE 1=1 ";
	public int getAllRecorderNumber( final String whereCondition ) {
		final List<Integer> result = new ArrayList<Integer>();
		String sql = SQL_GET_ACOUNT_PRODUCT + whereCondition;
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							result.add( rs.getInt(1));
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size()>0 )
			return (result.get(0)).intValue();
		else 
			return 0;
	}
	
	String SQL_GET_ALL_SOFTWARE=" SELECT s.*, t.name as softwareTypeName FROM  software s ,softwaretype t WHERE  s.softwareTypeId=t.id  ";
	public List<Software> getAllSoftware(int beginRecord, String whereCondition) {
		final List<Software> result = new ArrayList<Software>();
		String sql = SQL_GET_ALL_SOFTWARE+ whereCondition + "  ORDER BY id LIMIT " + beginRecord + ", "+  (beginRecord+Constant.PAGE_SIZE);
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
 					public void processRow(ResultSet rs) throws SQLException {
 						Software to= new Software();
 						to.setId(rs.getInt("id"));
						to.setName(rs.getString("name"));
						to.setSystem(rs.getString("system"));
						to.setSmallImagepath(rs.getString("smallImagepath"));
						to.setBigImagepath(rs.getString("bigImagepath"));
						to.setLastUpdate(rs.getString("lastUpdate"));
						to.setKeyword(rs.getString("keyword"));
						to.setDownCount(rs.getInt("downCount"));
						to.setCreateTime(rs.getTimestamp("createTime"));
						to.setFileName(rs.getString("fileName"));
						to.setSoftwareTypeId(rs.getInt("softwareTypeId"));
 						to.setSoftwareTypeName(rs.getString("softwareTypeName"));
 						to.setLangId(rs.getInt("langId"));
 						to.setContent(rs.getString("content"));
 						result.add(to);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
 

}
